﻿using Domain.Entitys.DB;
using Domain.Entitys.Inv;
using Domain.Entitys.Pinv;
using Domain.IRepositorys.Pinv;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys.Pinv
{
    public class PbRepository : IPbRepository
    {
        private readonly MyDbContext myDbContext;

        public PbRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        public int GetPbCount(string Pb_code, string Vendor_name)
        {
            IQueryable<Pinv_pb> query = myDbContext.pinv_Pb.Where(d => d.Del_flag == '0');
            if (!string.IsNullOrEmpty(Pb_code))
            {
                query = query.Where(d => d.Pb_code.Contains(Pb_code) || d.Po_code.Contains(Pb_code));
            }
            if (!string.IsNullOrEmpty(Vendor_name))
            {
                query = query.Where(d => d.Vendor_name.Contains(Vendor_name) || d.Vendor_code.Contains(Vendor_name));
            }
            return query.Count();
        }

        public List<Pb_details_Order> GetPbFyWhere(string Pb_code, string Vendor_name, int currentPage, int pageSize)
        {
            IQueryable<Pinv_pb> query = myDbContext.pinv_Pb
                .Where(d => d.Del_flag == '0')
                .Include(d => d.Details)
                .OrderByDescending(d => d.Create_time);  // 按创建时间降序排序

            if (!string.IsNullOrEmpty(Pb_code))
            {
                query = query.Where(d => d.Pb_code.Contains(Pb_code) || d.Po_code.Contains(Pb_code));
            }
            if (!string.IsNullOrEmpty(Vendor_name))
            {
                query = query.Where(d => d.Vendor_name.Contains(Vendor_name) || d.Vendor_code.Contains(Vendor_name));
            }

            // 直接使用数据库排序和分页，避免内存操作
            var pagedData = query
                .Skip((currentPage - 1) * pageSize)
                .Take(pageSize)
                .Select(d => new Pb_details_Order
                {
                    Pb_id = d.Pb_id,
                    Pb_code = d.Pb_code,
                    Po_id = d.Po_id,
                    Po_name = d.Po_name,
                    Po_code = d.Po_code,
                    Vendor_name = d.Vendor_name,
                    Vendor_code = d.Vendor_code,
                    Contacts_name = d.Contacts_name,
                    Contacts_phone = d.Contacts_phone,
                    Po_rep_id = d.Po_rep_id,
                    Po_dept_id = d.Po_dept_id,
                    Po_state = d.Po_state,
                    Pb_eligibility = d.Pb_eligibility,
                    Pb_Inboundtime = d.Pb_Inboundtime,
                    Pb_operator = d.Pb_operator,
                    Pb_Audit = d.Pb_Audit,
                    Pb_Audit_By = d.Pb_Audit_By,
                    Pb_Audit_Time = d.Pb_Audit_Time,
                    Pb_Audit_Remark = d.Pb_Audit_Remark,
                    Remark = d.Remark,
                    Create_by = d.Create_by,
                    Create_time = d.Create_time,
                    Update_by = d.Update_by,
                    Update_time = d.Update_time,
                    Del_flag = d.Del_flag,
                    Warehouse_code = d.Warehouse_code,
                    Zone_code = d.Zone_code,
                    Details = d.Details.Select(q => new Pb_details_Order.details
                    {
                        Purwhsedetail_id = q.Purwhsedetail_id,
                        Pb_id = q.Pb_id,
                        Pb_code = q.Pb_code,
                        Pro_name = q.Pro_name,
                        Pro_code = q.Pro_code,
                        Unitmeas_id = q.Unitmeas_id,
                        Pro_brand = q.Pro_brand,
                        Pro_model = q.Pro_model,
                        Details_monovalent = q.Details_monovalent,
                        Po_number = q.Po_number,
                        Details_storage = q.Details_storage,
                        Details_uq = q.Details_uq,
                        Podet_retqty = q.Podet_retqty,
                        To_be_stored_qty = q.To_be_stored_qty,
                        To_be_returned_qty = q.To_be_returned_qty,
                        Location_code = q.Location_code,
                    }).ToList()
                })
                .ToList();

            return pagedData;
        }

        public async Task<List<Pinv_purwhsedetail>> Getpurwhsedetailwherecode(string code)
        {
            return await myDbContext.Pinv_purwhsedetail.Where(d => d.Pb_code == code).ToListAsync();
        }

        public async Task<int> PostPbAdd(Pinv_pb pinv_Pb, List<Pinv_purwhsedetail> pinv_Purwhsedetails)
        {
            //通过 BeginTransactionAsync 开启一个异步数据库事务，确保后续的主表和明细表操作在同一事务中执行。
            //using 关键字确保事务在方法结束后自动释放（即使发生异常）。
            using var transaction = await myDbContext.Database.BeginTransactionAsync();
            try
            {
                //AddAsync 将采购订单主表实体 pinv_Po 加入数据库上下文（跟踪为 “新增” 状态)
                await myDbContext.AddAsync(pinv_Pb);
                //SaveChangesAsync 异步提交变更，将主表数据写入数据库。此时，主表的自增主键（如 Po_Id）和自动生成的订单编号（如 Po_Code）会被数据库赋值给 pinv_Po 对象
                await myDbContext.SaveChangesAsync();
                foreach(var pinv in pinv_Purwhsedetails)
                {
                    //主表保存后，其 Po_Id（主键）和 Po_Code（订单编号）已生成，通过循环为每个明细项设置这两个字段，建立明细表与主表的关联（外键关系）
                    pinv.Pb_id = pinv_Pb.Pb_id;// 关联主表的订单编号
                    pinv.Pb_code = pinv_Pb.Pb_code;// 关联主表的主键（外键）
                }
                //AddRangeAsync 批量将明细表集合加入数据库上下文
                await myDbContext.Pinv_purwhsedetail.AddRangeAsync(pinv_Purwhsedetails);
                //再次调用 SaveChangesAsync 提交明细表变更，将明细数据写入数据库。
                await myDbContext.SaveChangesAsync();
                // 全部操作成功则提交事务
                await transaction.CommitAsync();
                return 1;
            }
            catch (Exception)
            {
                //异常回滚事务
                await transaction.RollbackAsync();
                return 0;
            }
        }

        public async Task<int> PutPbDelete(Pinv_pb pinv_Pb)
        {
            Pinv_pb pb = await myDbContext.pinv_Pb.FindAsync(pinv_Pb.Pb_id);
            pb.Del_flag = pinv_Pb.Del_flag;
            return await myDbContext.SaveChangesAsync();
        }









        public async Task<int> PutPbUpdate(Pinv_pb pinv_pb, List<Pinv_purwhsedetail> pinv_Purwhsedetail)
{
    using var transaction = await myDbContext.Database.BeginTransactionAsync();
    try
    {
        // 1. 更新主表（使用不跟踪查询）
        var pb = await myDbContext.pinv_Pb
            .FirstOrDefaultAsync(d => d.Pb_id == pinv_pb.Pb_id);
        if (pb == null) return 0;

        // 更新主表属性
        pb.Pb_Inboundtime = pinv_pb.Pb_Inboundtime;
        pb.Pb_operator = pinv_pb.Pb_operator;
        pb.Warehouse_code = pinv_pb.Warehouse_code;
        pb.Zone_code = pinv_pb.Zone_code;
        pb.Location_code = pinv_pb.Location_code;
        pb.Remark = pinv_pb.Remark;
        pb.Update_by = pinv_pb.Update_by;
        pb.Update_time = DateTime.Now;
        myDbContext.Update(pb);
        await myDbContext.SaveChangesAsync();

        // 2. 查询旧明细（不跟踪）
        var oldDetails = await myDbContext.Pinv_purwhsedetail
            .AsNoTracking()
            .Where(d => d.Pb_code == pb.Pb_code)
            .ToListAsync();

        // 识别三类记录
        var pbAdd = new List<Pinv_purwhsedetail>();
        var pbUpdate = new List<Pinv_purwhsedetail>();
        var pbDeleteIds = new List<int>();

        // 遍历传入的数据
        foreach(var newitem in pinv_Purwhsedetail)
        {
            // 无主键或主键不在旧列表中
            if (newitem.Purwhsedetail_id == 0 || !oldDetails.Any(old => old.Purwhsedetail_id == newitem.Purwhsedetail_id))
            {
                pbAdd.Add(newitem);
            }
            else
            {
                var olditem = oldDetails.First(old => old.Purwhsedetail_id == newitem.Purwhsedetail_id);
                if (IsModified(olditem, newitem))
                {
                    pbUpdate.Add(newitem);
                }
            }
        }

        // 获取要删除的ID列表
        pbDeleteIds = oldDetails
            .Where(old => !pinv_Purwhsedetail.Any(newItem => newItem.Purwhsedetail_id == old.Purwhsedetail_id))
            .Select(old => old.Purwhsedetail_id)
            .ToList();

        // 执行差异化操作
        if (pbDeleteIds.Any())
        {
            var entitiesToDelete = await myDbContext.Pinv_purwhsedetail
                .Where(e => pbDeleteIds.Contains(e.Purwhsedetail_id))
                .ToListAsync();
            myDbContext.RemoveRange(entitiesToDelete);
        }

        // 更新现有记录
        foreach(var item in pbUpdate)
        {
            var existing = await myDbContext.Pinv_purwhsedetail
                .FindAsync(item.Purwhsedetail_id);
            if (existing != null)
            {
                myDbContext.Entry(existing).CurrentValues.SetValues(item);
            }
        }

        // 添加新记录
        foreach(var item in pbAdd)
        {
            item.Pb_code = pb.Pb_code;
            item.Pb_id = pb.Pb_id;
            await myDbContext.AddAsync(item);
        }

        await myDbContext.SaveChangesAsync();
        await transaction.CommitAsync();
        return 1;
    }
    catch (Exception ex)
    {
        await transaction.RollbackAsync();
        // 记录日志
        Console.WriteLine($"Error in PutPbUpdate: {ex.Message}\n{ex.StackTrace}");
        return 0;
    }
}

        // 辅助方法：检查明细字段是否被修改（根据实际业务调整）
        private bool IsModified(Pinv_purwhsedetail oldItem, Pinv_purwhsedetail newItem)
        {
            return oldItem.To_be_stored_qty != newItem.To_be_stored_qty ||
                   oldItem.Location_code != newItem.Location_code;
        }











        public async Task<int> PutPodetails_Pb_PbdetailsUpdate(
            Pinv_Po pinv_Po,
            List<pinv_details> pinv_Details,
            Pinv_pb pinv_Pb,
            List<Pinv_purwhsedetail> pinv_Purwhsedetail)
        {
            // 输入验证（提前拦截无效数据）
            if (pinv_Pb == null || pinv_Pb.Pb_id <= 0)
                return 2; // 无效的Pb_id
            if (pinv_Purwhsedetail == null || !pinv_Purwhsedetail.Any())
                return 3; // 采购明细不能为空

            using (var transaction = await myDbContext.Database.BeginTransactionAsync())
            {
                try
                {
                    // 1. 处理审核信息更新
                    var pbEntity = await myDbContext.pinv_Pb.FindAsync(pinv_Pb.Pb_id);
                    if (pbEntity == null)
                        return 4; // 未找到对应的Pb记录

                    pbEntity.Pb_Audit = pinv_Pb.Pb_Audit;
                    pbEntity.Pb_Audit_By = pinv_Pb.Pb_Audit_By;
                    pbEntity.Pb_Audit_Time = pinv_Pb.Pb_Audit_Time;
                    pbEntity.Pb_Audit_Remark = pinv_Pb.Pb_Audit_Remark;
                    myDbContext.Entry(pbEntity).State = EntityState.Modified;

                    // 2. 批量更新pinv_details（采购明细入库处理）
                    var detailIds = pinv_Details.Select(d => d.Details_id).ToList();
                    var existingDetailsDict = await myDbContext.pinv_details
                        .Where(d => detailIds.Contains(d.Details_id))
                        .ToDictionaryAsync(d => d.Details_id);

                    var purwhseByProCode = pinv_Purwhsedetail
                        .ToDictionary(pd => pd.Pro_code, pd => pd); // 按产品代码索引采购明细

                    var modifiedDetails = new List<pinv_details>();
                    foreach (var detail in pinv_Details)
                    {
                        if (existingDetailsDict.TryGetValue(detail.Details_id, out var existingDetail))
                        {
                            // 匹配产品对应的采购明细并更新库存
                            if (purwhseByProCode.TryGetValue(existingDetail.Pro_code, out var purDetail))
                            {
                                existingDetail.Details_storage += purDetail.To_be_stored_qty;
                                modifiedDetails.Add(existingDetail);
                                myDbContext.Entry(existingDetail).State = EntityState.Modified;
                            }
                        }
                    }

                    // 3. 更新Pinv_Po状态（与Pinv_Pb状态逻辑保持一致）
                    if (pinv_Po?.Po_Id > 0)
                    {
                        var existingPo = await myDbContext.Pinv_Po.FindAsync(pinv_Po.Po_Id);
                        if (existingPo != null)
                        {
                            // 获取所有相关明细（包括未修改的）
                            var allPoDetails = await myDbContext.pinv_details
                                .Where(d => d.Po_Id == pinv_Po.Po_Id)
                                .ToListAsync();

                            // 合并修改后的明细数据
                            var mergedPoDetails = allPoDetails.Select(d =>
                                modifiedDetails.FirstOrDefault(m => m.Details_id == d.Details_id) ?? d
                            ).ToList();

                            // 判断状态条件
                            bool allFullyStored = mergedPoDetails.All(d => d.Po_number == d.Details_storage);
                            bool hasPartialStored = mergedPoDetails.Any(d => d.Details_storage > 0);

                            existingPo.Po_State = allFullyStored ? 3 : (hasPartialStored ? 2 : 0);
                            myDbContext.Entry(existingPo).State = EntityState.Modified;
                        }
                    }

                    // 4. 更新Pinv_purwhsedetails（采购入库明细）
                    var purDetailIds = pinv_Purwhsedetail.Select(p => p.Purwhsedetail_id).ToList();
                    var existingPurDetailsDict = await myDbContext.Pinv_purwhsedetail
                        .Where(p => purDetailIds.Contains(p.Purwhsedetail_id))
                        .ToDictionaryAsync(p => p.Purwhsedetail_id);

                    foreach (var purDetail in pinv_Purwhsedetail)
                    {
                        if (existingPurDetailsDict.TryGetValue(purDetail.Purwhsedetail_id, out var existingPur))
                        {
                            existingPur.Details_storage = purDetail.Details_storage;
                            existingPur.To_be_stored_qty = 0; // 清空待入库数量
                            myDbContext.Entry(existingPur).State = EntityState.Modified;
                        }
                    }

                    // 5. 更新Pinv_Pb状态（与Po状态逻辑保持一致）
                    var allPbDetails = await myDbContext.Pinv_purwhsedetail
                        .Where(d => d.Pb_id == pinv_Pb.Pb_id)
                        .ToListAsync();

                    bool pbAllFullyStored = allPbDetails.All(d => d.Po_number == d.Details_storage);
                    bool pbHasPartialStored = allPbDetails.Any(d => d.Details_storage > 0);
                    pbEntity.Po_state = pbAllFullyStored ? 3 : (pbHasPartialStored ? 2 : 0);

                    // 6. 处理库存更新
                    foreach (var purDetail in pinv_Purwhsedetail)
                    {
                        // 验证采购明细必要字段
                        if (string.IsNullOrEmpty(purDetail.Pro_code) || purDetail.To_be_stored_qty <= 0)
                            continue;

                        // 查询产品信息
                        var product = await myDbContext.Db_Product
                            .FirstOrDefaultAsync(p => p.Pro_code == purDetail.Pro_code);
                        if (product == null)
                            return 5; // 产品不存在

                        // 查询库存记录（按产品+仓库+区域+库位唯一确定）
                        var stock = await myDbContext.inv_stock
                            .FirstOrDefaultAsync(s =>
                                s.pro_code == purDetail.Pro_code &&
                                s.Warehouse_Code == pbEntity.Warehouse_code &&
                                s.Zone_Code == pbEntity.Zone_code &&
                                s.Location_Code == purDetail.Location_code);

                        if (stock != null)
                        {
                            // 更新现有库存
                            stock.stock_numder += purDetail.To_be_stored_qty;
                            stock.Update_Time = DateTime.Now; // 增加更新时间
                        }
                        else
                        {
                            var cw = await myDbContext.db_warehouse.FirstOrDefaultAsync(d => d.Warehouse_code == pbEntity.Warehouse_code);
                            var kw = await myDbContext.db_warehouse_zone.FirstOrDefaultAsync(d => d.zone_code == pbEntity.Zone_code);
                            var kq = await myDbContext.db_warehouse_location.FirstOrDefaultAsync(d => d.location_code == purDetail.Location_code);

                            // 创建新库存记录
                            myDbContext.inv_stock.Add(new inv_stock
                            {
                                pro_code = purDetail.Pro_code,
                                stock_name = product.Pro_name,
                                pro_brand = product.Pro_brand,
                                Warehouse_Code = pbEntity.Warehouse_code,
                                Warehouse_Name = cw.Warehouse_name,
                                stock_numder = purDetail.To_be_stored_qty,
                                dict_code = product.Measure_id,
                                stock_model = product.Pro_model,
                                Pro_attribute_id = product.Pro_attribute_id,
                                pro_cost_price = purDetail.Details_monovalent,
                                Pro_sale_price = product.Pro_sale_price,
                                stock_entry_no = pbEntity.Pb_code,
                                Zone_Code = pbEntity.Zone_code,
                                Zone_Name = kw.zone_name,
                                Location_Code = purDetail.Location_code,
                                Location_Name = kq.location_name,
                                Vendor_Code = pbEntity.Vendor_code,
                                Vendor_Name = pbEntity.Vendor_name,
                                create_by = pbEntity.Create_by,
                                Create_Time = DateTime.Now,
                                Update_Time = DateTime.Now, // 新增更新时间字段
                                Del_Flag = '0'
                            });
                        }
                    }

                    // 保存所有更改
                    int affectedRows = await myDbContext.SaveChangesAsync();
                    await transaction.CommitAsync();
                    return 1; // 成功
                }
                catch (Exception ex)
                {
                    await transaction.RollbackAsync();
                    // 建议记录异常日志（例如使用ILogger）
                    // _logger.LogError(ex, "更新采购明细时发生错误");
                    return 10; // 异常错误
                }
            }
        }

        public Task<int> PutPoUpdateAudit(Pinv_pb pinv_pb)
        {
            Pinv_pb pb = myDbContext.pinv_Pb.Find(pinv_pb.Pb_id);
            pb.Pb_Audit = pinv_pb.Pb_Audit;
            pb.Pb_Audit_By = pinv_pb.Pb_Audit_By;
            pb.Pb_Audit_Time = pinv_pb.Pb_Audit_Time;
            pb.Pb_Audit_Remark = pinv_pb.Pb_Audit_Remark;
            myDbContext.pinv_Pb.Update(pb);
            return myDbContext.SaveChangesAsync();
        }

        public List<Pb_details_Order> GetPbFyWhereinfo(string Pb_code, string Vendor_name, int currentPage, int pageSize)
        {
                IQueryable<Pinv_pb> query = myDbContext.pinv_Pb
                    .Where(d => d.Del_flag == '0'&&d.Pb_Audit=='1')
                    .Include(d => d.Details); // 确保加载 Details 导航属性

                if (!string.IsNullOrEmpty(Pb_code))
                {
                    query = query.Where(d => d.Pb_code.Contains(Pb_code) || d.Po_code.Contains(Pb_code));
                }
                if (!string.IsNullOrEmpty(Vendor_name))
                {
                    query = query.Where(d => d.Vendor_name.Contains(Vendor_name) || d.Vendor_code.Contains(Vendor_name));
                }

                // 先查询所有数据到内存
            //    var allData = query.ToList();

            //var groupedData = allData
            //    .GroupBy(d => d.Po_id)
            //    .SelectMany(g => g) // 获取每个分组中的所有记录
            //    .ToList();

            // 分页
            var pagedData = query
                    .Skip((currentPage - 1) * pageSize)
                    .Take(pageSize)
                    .Select(d => new Pb_details_Order
                    {
                        Pb_id = d.Pb_id,
                        Pb_code = d.Pb_code,
                        Po_id = d.Po_id,
                        Po_name = d.Po_name,
                        Po_code = d.Po_code,
                        Vendor_name = d.Vendor_name,
                        Vendor_code = d.Vendor_code,
                        Contacts_name = d.Contacts_name,
                        Contacts_phone = d.Contacts_phone,
                        Po_rep_id = d.Po_rep_id,
                        Po_dept_id = d.Po_dept_id,
                        Po_state = d.Po_state,
                        Pb_eligibility = d.Pb_eligibility,
                        Pb_Inboundtime = d.Pb_Inboundtime,
                        Pb_operator = d.Pb_operator,
                        Pb_Audit = d.Pb_Audit,
                        Pb_Audit_By = d.Pb_Audit_By,
                        Pb_Audit_Time = d.Pb_Audit_Time,
                        Pb_Audit_Remark = d.Pb_Audit_Remark,
                        Remark = d.Remark,
                        Create_by = d.Create_by,
                        Create_time = d.Create_time,
                        Update_by = d.Update_by,
                        Update_time = d.Update_time,
                        Del_flag = d.Del_flag,
                        Warehouse_code = d.Warehouse_code,
                        Zone_code = d.Zone_code,
                        Details = d.Details.Select(q => new Pb_details_Order.details
                        {
                            Purwhsedetail_id = q.Purwhsedetail_id,
                            Pb_id = q.Pb_id,
                            Pb_code = q.Pb_code,
                            Pro_name = q.Pro_name,
                            Pro_code = q.Pro_code,
                            Unitmeas_id = q.Unitmeas_id,
                            Pro_brand = q.Pro_brand,
                            Pro_model = q.Pro_model,
                            Details_monovalent = q.Details_monovalent,
                            Po_number = q.Po_number,
                            Details_storage = q.Details_storage,
                            Details_uq = q.Details_uq,
                            Podet_retqty = q.Podet_retqty,
                            To_be_stored_qty = q.To_be_stored_qty,
                            To_be_returned_qty = q.To_be_returned_qty,
                            Location_code = q.Location_code,
                        }).ToList()
                    })
                    .ToList();

                return pagedData;
            
        }

        public int GetPbCountinfo(string Pb_code, string Vendor_name)
        {
            IQueryable<Pinv_pb> query = myDbContext.pinv_Pb.Where(d => d.Del_flag == '0'&&d.Pb_Audit=='1');
            if (!string.IsNullOrEmpty(Pb_code))
            {
                query = query.Where(d => d.Pb_code.Contains(Pb_code) || d.Po_code.Contains(Pb_code));
            }
            if (!string.IsNullOrEmpty(Vendor_name))
            {
                query = query.Where(d => d.Vendor_name.Contains(Vendor_name) || d.Vendor_code.Contains(Vendor_name));
            }
            return query.Count();
        }
    }
}
